﻿/****** Object:  Table [dbo].[Person]    Script Date: 08/26/2008 21:39:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Person]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Person](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[UniqueId] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Person_UniqueId]  DEFAULT (newsequentialid()),
	[FirstName] [nvarchar](256) NOT NULL,
	[LastName] [nvarchar](256) NOT NULL,
	[EMail] [nvarchar](512) NULL,
	[Address] [ntext] NULL,
	[PostalCode] [ntext] NULL,
	[City] [ntext] NULL,
	[Country] [ntext] NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[Category]    Script Date: 08/26/2008 21:39:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Category]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Category](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[UniqueId] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Category_UniqueId]  DEFAULT (newsequentialid()),
	[ParentId] [int] NULL,
	[Path] [nvarchar](1024) NULL,
	[Name] [nvarchar](100) NOT NULL,
	[Title] [nvarchar](256) NOT NULL,
	[Summary] [ntext] NOT NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_Category_UniqueId] UNIQUE NONCLUSTERED 
(
	[UniqueId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO

/****** Object:  Index [IX_Category_Name]    Script Date: 08/26/2008 21:39:48 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Category]') AND name = N'IX_Category_Name')
CREATE UNIQUE NONCLUSTERED INDEX [IX_Category_Name] ON [dbo].[Category] 
(
	[ParentId] ASC,
	[Name] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

/****** Object:  Trigger [dbo].[Category_BuildCategoryPath]    Script Date: 08/26/2008 21:39:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[Category_BuildCategoryPath]'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER [dbo].[Category_BuildCategoryPath]
ON [dbo].[Category]
AFTER INSERT, UPDATE
AS
	DECLARE @category TABLE
	(
		Id int
	);

	WITH CategoryCTE(Id) AS
	(
		SELECT a.Id FROM Category a
		INNER JOIN Inserted i
		ON a.Id = i.Id
		UNION ALL
		SELECT c.Id FROM Category c
		INNER JOIN CategoryCTE cte ON c.ParentId = cte.Id
	)
	INSERT INTO @category(Id)
		SELECT Id FROM CategoryCTE;
		
	UPDATE Category SET Path = dbo.GetCategoryPath(Id) WHERE Id IN (SELECT Id FROM @category);
	UPDATE Post SET Path = dbo.GetPostPath(Id) WHERE CategoryId IN (SELECT Id FROM @category);' 
GO
/****** Object:  Table [dbo].[Post]    Script Date: 08/26/2008 21:39:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Post]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Post](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[UniqueId] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Post_UniqueId]  DEFAULT (newsequentialid()),
	[CategoryId] [int] NOT NULL,
	[Path] [nvarchar](1024) NULL,
	[Name] [nvarchar](100) NOT NULL,
	[Title] [nvarchar](255) NOT NULL,
	[Summary] [ntext] NOT NULL,
	[CreationDate] [datetime] NOT NULL CONSTRAINT [DF_Post_CreationDate]  DEFAULT (getdate()),
	[CreatedByUserId] [int] NOT NULL,
	[ModificationDate] [datetime] NOT NULL CONSTRAINT [DF_Post_ModificationDate]  DEFAULT (getdate()),
	[ModifiedByUserId] [int] NOT NULL,
 CONSTRAINT [PK_Post] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_Post_UniqueId] UNIQUE NONCLUSTERED 
(
	[UniqueId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO

/****** Object:  Index [IX_Post_Name]    Script Date: 08/26/2008 21:39:48 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Post]') AND name = N'IX_Post_Name')
CREATE UNIQUE NONCLUSTERED INDEX [IX_Post_Name] ON [dbo].[Post] 
(
	[CategoryId] ASC,
	[Name] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

/****** Object:  Trigger [dbo].[Post_BuildPostPath]    Script Date: 08/26/2008 21:39:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[Post_BuildPostPath]'))
EXEC dbo.sp_executesql @statement = N'Create TRIGGER [dbo].[Post_BuildPostPath]
ON [dbo].[Post]
AFTER INSERT, UPDATE
AS		
	UPDATE Post SET Path = dbo.GetPostPath(Id) WHERE Id IN (SELECT Id FROM Inserted);
' 
GO
/****** Object:  Table [dbo].[ArticlePost]    Script Date: 08/26/2008 21:39:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ArticlePost]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ArticlePost](
	[Id] [int] NOT NULL,
	[Content] [ntext] NOT NULL,
	[AuthorId] [int] NULL,
 CONSTRAINT [PK_ArticlePost] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[User]    Script Date: 08/26/2008 21:39:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[User]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[User](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[UniqueId] [uniqueidentifier] NOT NULL CONSTRAINT [DF_User_UniqueId]  DEFAULT (newsequentialid()),
	[Username] [nvarchar](100) NOT NULL,
	[Password] [nvarchar](100) NULL,
	[PersonId] [int] NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

/****** Object:  Index [IX_User_PersonId]    Script Date: 08/26/2008 21:39:48 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[User]') AND name = N'IX_User_PersonId')
CREATE UNIQUE NONCLUSTERED INDEX [IX_User_PersonId] ON [dbo].[User] 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[GalleryPost]    Script Date: 08/26/2008 21:39:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GalleryPost]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[GalleryPost](
	[Id] [int] NOT NULL,
 CONSTRAINT [PK_GalleryPost] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[EventPost]    Script Date: 08/26/2008 21:39:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EventPost]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[EventPost](
	[Id] [int] NOT NULL,
 CONSTRAINT [PK_EventPost] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  UserDefinedFunction [dbo].[GetCategoryPath]    Script Date: 08/26/2008 21:39:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCategoryPath]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[GetCategoryPath]
	(
	@id int
	)
RETURNS nvarchar(1024)
AS
	BEGIN
	DECLARE @path nvarchar(1024);

	WITH CategoryCTE AS 
	(
		SELECT Id, CAST(''/'' AS nvarchar(1024)) AS Path
		FROM dbo.Category AS a
		WHERE (ParentId IS NULL)
		UNION ALL
		SELECT c.Id, CAST(r.Path + c.Name + ''/'' AS nvarchar(1024)) AS Path
		FROM dbo.Category AS c 
		INNER JOIN
		CategoryCTE AS r ON c.ParentId = r.Id
	)
	SELECT @path = Path FROM CategoryCTE WHERE Id = @id;

	RETURN @path;
	END
' 
END

GO
/****** Object:  UserDefinedFunction [dbo].[GetPostPath]    Script Date: 08/26/2008 21:39:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetPostPath]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[GetPostPath]
	(
	@id int
	)
RETURNS nvarchar(1024)
AS
	BEGIN
	DECLARE @path nvarchar(1024);
	
	SELECT @path = (dbo.GetCategoryPath(p.CategoryId) + p.Name)
	FROM dbo.Post AS p 
	WHERE Id = @id;
	
	RETURN @path;
	END
' 
END

GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Category_Category]') AND parent_object_id = OBJECT_ID(N'[dbo].[Category]'))
ALTER TABLE [dbo].[Category]  WITH CHECK ADD  CONSTRAINT [FK_Category_Category] FOREIGN KEY([ParentId])
REFERENCES [dbo].[Category] ([Id])
GO
ALTER TABLE [dbo].[Category] CHECK CONSTRAINT [FK_Category_Category]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Post_Category]') AND parent_object_id = OBJECT_ID(N'[dbo].[Post]'))
ALTER TABLE [dbo].[Post]  WITH CHECK ADD  CONSTRAINT [FK_Post_Category] FOREIGN KEY([CategoryId])
REFERENCES [dbo].[Category] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Post] CHECK CONSTRAINT [FK_Post_Category]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Post_CreatedByUser]') AND parent_object_id = OBJECT_ID(N'[dbo].[Post]'))
ALTER TABLE [dbo].[Post]  WITH CHECK ADD  CONSTRAINT [FK_Post_CreatedByUser] FOREIGN KEY([CreatedByUserId])
REFERENCES [dbo].[User] ([Id])
GO
ALTER TABLE [dbo].[Post] CHECK CONSTRAINT [FK_Post_CreatedByUser]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Post_ModifiedByUser]') AND parent_object_id = OBJECT_ID(N'[dbo].[Post]'))
ALTER TABLE [dbo].[Post]  WITH CHECK ADD  CONSTRAINT [FK_Post_ModifiedByUser] FOREIGN KEY([ModifiedByUserId])
REFERENCES [dbo].[User] ([Id])
GO
ALTER TABLE [dbo].[Post] CHECK CONSTRAINT [FK_Post_ModifiedByUser]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ArticlePost_Person]') AND parent_object_id = OBJECT_ID(N'[dbo].[ArticlePost]'))
ALTER TABLE [dbo].[ArticlePost]  WITH CHECK ADD  CONSTRAINT [FK_ArticlePost_Person] FOREIGN KEY([AuthorId])
REFERENCES [dbo].[Person] ([Id])
ON UPDATE CASCADE
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[ArticlePost] CHECK CONSTRAINT [FK_ArticlePost_Person]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ArticlePost_Post]') AND parent_object_id = OBJECT_ID(N'[dbo].[ArticlePost]'))
ALTER TABLE [dbo].[ArticlePost]  WITH CHECK ADD  CONSTRAINT [FK_ArticlePost_Post] FOREIGN KEY([Id])
REFERENCES [dbo].[Post] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[ArticlePost] CHECK CONSTRAINT [FK_ArticlePost_Post]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_User_Person]') AND parent_object_id = OBJECT_ID(N'[dbo].[User]'))
ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_Person] FOREIGN KEY([PersonId])
REFERENCES [dbo].[Person] ([Id])
ON UPDATE CASCADE
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_Person]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_GalleryPost_Post]') AND parent_object_id = OBJECT_ID(N'[dbo].[GalleryPost]'))
ALTER TABLE [dbo].[GalleryPost]  WITH CHECK ADD  CONSTRAINT [FK_GalleryPost_Post] FOREIGN KEY([Id])
REFERENCES [dbo].[Post] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[GalleryPost] CHECK CONSTRAINT [FK_GalleryPost_Post]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_EventPost_Post]') AND parent_object_id = OBJECT_ID(N'[dbo].[EventPost]'))
ALTER TABLE [dbo].[EventPost]  WITH CHECK ADD  CONSTRAINT [FK_EventPost_Post] FOREIGN KEY([Id])
REFERENCES [dbo].[Post] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[EventPost] CHECK CONSTRAINT [FK_EventPost_Post]
